﻿using System;
using System.Data.SQLite;
using System.Collections.Generic;

// 实体类定义
public class School
{
    public int Id { get; set; }
    public string Name { get; set; }
    public DateTime EstablishedDate { get; set; }
}

public class Class
{
    public int Id { get; set; }
    public int SchoolId { get; set; }
    public string ClassName { get; set; }
    public string Teacher { get; set; }
}

public class Student
{
    public int Id { get; set; }
    public int ClassId { get; set; }
    public string Name { get; set; }
    public DateTime BirthDate { get; set; }
    public string Gender { get; set; }
}

public class LogEntry
{
    public int Id { get; set; }
    public DateTime Timestamp { get; set; }
    public string OperationType { get; set; }
    public string Details { get; set; }
}

// 数据库管理类
public class StudentDBManager : IDisposable
{
    private readonly SQLiteConnection _connection;

    public StudentDBManager(string connectionString)
    {
        _connection = new SQLiteConnection(connectionString);
        _connection.Open();
        InitializeDatabase();
    }

    private void InitializeDatabase()
    {
        ExecuteNonQuery(@"
            CREATE TABLE IF NOT EXISTS Schools (
                Id INTEGER PRIMARY KEY AUTOINCREMENT,
                Name TEXT NOT NULL,
                EstablishedDate DATETIME NOT NULL
            );

            CREATE TABLE IF NOT EXISTS Classes (
                Id INTEGER PRIMARY KEY AUTOINCREMENT,
                SchoolId INTEGER NOT NULL,
                ClassName TEXT NOT NULL,
                Teacher TEXT,
                FOREIGN KEY(SchoolId) REFERENCES Schools(Id)
            );

            CREATE TABLE IF NOT EXISTS Students (
                Id INTEGER PRIMARY KEY AUTOINCREMENT,
                ClassId INTEGER NOT NULL,
                Name TEXT NOT NULL,
                BirthDate DATETIME NOT NULL,
                Gender TEXT CHECK(Gender IN ('Male', 'Female')),
                FOREIGN KEY(ClassId) REFERENCES Classes(Id)
            );

            CREATE TABLE IF NOT EXISTS Logs (
                Id INTEGER PRIMARY KEY AUTOINCREMENT,
                Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
                OperationType TEXT NOT NULL,
                Details TEXT
            );");
    }

    // 基础操作方法
    public int AddSchool(School school)
    {
        return ExecuteNonQuery(
            "INSERT INTO Schools (Name, EstablishedDate) VALUES (@name, @date)",
            ("@name", school.Name),
            ("@date", school.EstablishedDate),
            LogOperation("AddSchool", $"Added school: {school.Name}")
        );
    }

    public List<School> GetAllSchools()
    {
        return Query<School>("SELECT * FROM Schools", reader => new School {
            Id = reader.GetInt32(0),
            Name = reader.GetString(1),
            EstablishedDate = reader.GetDateTime(2)
        });
    }

    // 类似实现 Class 和 Student 的CRUD方法...

    // 日志操作
    private (string, object) LogOperation(string operationType, string details)
    {
        return ("@log", (Action)(() => ExecuteNonQuery(
            "INSERT INTO Logs (OperationType, Details) VALUES (@type, @details)",
            ("@type", operationType),
            ("@details", details)
        )));
    }

    public List<LogEntry> GetLogs(DateTime? start = null, DateTime? end = null)
    {
        var query = "SELECT * FROM Logs";
        var parameters = new List<(string, object)>();
        
        if (start.HasValue || end.HasValue)
        {
            query += " WHERE ";
            var conditions = new List<string>();
            
            if (start.HasValue)
            {
                conditions.Add("Timestamp >= @start");
                parameters.Add(("@start", start.Value));
            }
            
            if (end.HasValue)
            {
                conditions.Add("Timestamp <= @end");
                parameters.Add(("@end", end.Value));
            }
            
            query += string.Join(" AND ", conditions);
        }

        return Query<LogEntry>(query, reader => new LogEntry {
            Id = reader.GetInt32(0),
            Timestamp = reader.GetDateTime(1),
            OperationType = reader.GetString(2),
            Details = reader.GetString(3)
        }, parameters.ToArray());
    }

    private int ExecuteNonQuery(string sql, params (string, object)[] parameters)
    {
        using var cmd = new SQLiteCommand(sql, _connection);
        AddParameters(cmd, parameters);
        return cmd.ExecuteNonQuery();
    }

    private List<T> Query<T>(string sql, Func<SQLiteDataReader, T> mapper, params (string, object)[] parameters)
    {
        var results = new List<T>();
        using var cmd = new SQLiteCommand(sql, _connection);
        AddParameters(cmd, parameters);
        
        using var reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            results.Add(mapper(reader));
        }
        return results;
    }

    private void AddParameters(SQLiteCommand cmd, (string, object)[] parameters)
    {
        foreach (var (name, value) in parameters)
        {
            if (value is Action action)
            {
                action();
                continue;
            }
            
            cmd.Parameters.AddWithValue(name, value ?? DBNull.Value);
        }
    }

    public void Dispose() => _connection.Dispose();
}

class Program
{
    static void Main()
    {
        const string connStr = "Data Source=student.db;Version=3;";
        
        using var db = new StudentDBManager(connStr);

        var school = new School {
            Name = "第一中学",
            EstablishedDate = new DateTime(1950, 9, 1)
        };
        db.AddSchool(school);

        // 查询日志
        var logs = db.GetLogs(DateTime.Today.AddDays(-1), DateTime.Today);
        foreach (var log in logs)
        {
            Console.WriteLine($"[{log.Timestamp}] {log.OperationType}: {log.Details}");
        }
    }
}
